<html>
<META http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<head>
<title>Section 12.1.&nbsp; Changing Database Objects from PHP</title>
<link rel="STYLESHEET" type="text/css" href="images/style.css">
<link rel="STYLESHEET" type="text/css" href="images/docsafari.css">
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-2.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<br><table width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top"><a name="learnphpmysql-CHP-12-SECT-1"></a>
<h3 id="title-IDAS0D1L" class="docSection1Title">12.1. Changing Database Objects from PHP</h3>
<a name="IDX-CHP-12-0581"></a> 
<a name="IDX-CHP-12-0582"></a> 

<p class="docText">The SQL query string remains the common tool for giving database commands. We can just as easily create and modify database objects with standard SQL that is called the same way we execute queries. We'll begin with creating a table.</p>
<a name="learnphpmysql-CHP-12-SECT-1.1"></a>
<h4 id="title-IDAE1D1L" class="docSection2Title">12.1.1. Creating a Table</h4>
<p class="docText">We've previously created the <tt>books</tt> and <tt>authors</tt> tables but we haven't created the <tt>purchases</tt> table. We'll create one using the PHP in <a class="docLink" href="#learnphpmysql-CHP-12-EX-1">Example 12-1</a>.</p>
<a name="learnphpmysql-CHP-12-EX-1"></a><h5 id="title-IDAY1D1L" class="docExampleTitle">Example 12-1. Creating a table from a PHP page in create_table.php</h5><p><table cellspacing="0" width="90%" border="1" cellpadding="5"><tr><td>

<pre>
&lt;?php
include('db_login.php');
require_once( 'DB.php' );
$connection = DB::connect( "mysql://$db_username:$db_password@$db_host/
$db_database");
if (!$connection)
{
  die ("Could not connect to the database: &lt;br&gt;". DB::errorMessage());
};
<span class="docEmphStrong">$query = '</span>
<span class="docEmphStrong">CREATE TABLE `purchases` (</span>
  <span class="docEmphStrong">`purchase_id` int(11) NOT NULL auto_increment</span>,
  <span class="docEmphStrong">`user_id` varchar(10) NOT NULL</span>,
  <span class="docEmphStrong">`title_id` int(11) NOT NULL</span>,
  <span class="docEmphStrong">`purchased` timestamp NOT NULL</span>,
  <span class="docEmphStrong">PRIMARY KEY  (`purchase_id`)</span>
<span class="docEmphStrong">)</span>
<span class="docEmphStrong">';</span>
echo ("Table created successfully!");
$result = $connection-&gt;query($query);
if (DB::isError($result))
{
  die ("Could not query the database: &lt;br&gt;". $query. " ".DB::errorMessage($result));
}
$connection-&gt;disconnect();
?&gt;
</pre><br>

</TD></tr></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-12-EX-1">Example 12-1</a> has the same <tt>create</tt> statement bolded that you'd use directly from the command line. The statement is assigned to the <tt>$query</tt> variable as a string. When <tt>query</tt> is executed, you no longer get a result set. Instead, the table is created. We see this as the result:</P>
<pre>
Table created successfully!
</pre><BR>

<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-12-FIG-1">Figure 12-1</a> shows the describe (<tt>desc</tt>) command for the table from the <tt>mysql</tt> command-line client.</p>
<a name="learnphpmysql-CHP-12-FIG-1"></a><P><center>
<h5 class="docFigureTitle">Figure 12-1. Our purchases table defined from a PHP script appears everywhere</h5>
<img border="0" alt="" width="549" height="145" SRC="images/learnphpmysql_1201.jpg">
</center></p><BR>
<p class="docText">You could just as easily have substituted another database command.</p>
<p><table border="0" bgcolor="black" cellspacing="0" cellpadding="1" width="90%" align="center"><TR><td><table bgcolor="white" width="100%" border="0" cellspacing="0" cellpadding="6"><tr><TD width="60" valign="top"><img src="images/warning_yellow.jpg" width="51" height="36" alt=""></td><td valign="top">
<p class="docText">In general, commands to modify databases and tables should be kept out of your PHP code to reduce the risk of a malicious user exploiting them or plain old programming mistakes that could wipe out a lot of data. We discuss them to illustrate what can be done from PHP. The only time you're likely to use these commands directly in PHP code is if you're writing a utility for web-based administration of MySQL databases such as phpMyAdmin.</p>
<p class="docText">If you really feel the need to use modification commands, place them in a portion of your site that is either password-protected at the Apache web server level or access-protected through your PHP code. We'll discuss restricting access to pages and logging in users in <a class="docLink" href="learnphpmysql-CHP-13.html#learnphpmysql-CHP-13">Chapter 13</a>. With that caution in place, we'll discuss dropping tables next.</p>
</TD></TR></table></td></tr></table></p><br>

<a name="learnphpmysql-CHP-12-SECT-1.2"></a>
<H4 id="title-IDA54D1L" class="docSection2Title">12.1.2. Dropping a Table</h4>
<a name="IDX-CHP-12-0583"></a> 
<a name="IDX-CHP-12-0584"></a> 
<a name="IDX-CHP-12-0585"></a> 
<a name="IDX-CHP-12-0586"></a> 

<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-12-EX-2">Example 12-2</a> drops the table you just created.</P>
<a name="learnphpmysql-CHP-12-EX-2"></a><h5 id="title-IDA45D1L" class="docExampleTitle">Example 12-2. Dropping the purchases table in drop.php</H5><P><table cellspacing="0" width="90%" border="1" cellpadding="5"><TR><td>

<pre>
&lt;?php
require_once('db_login.php');
require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: &lt;br /&gt;". DB::errorMessage($connection));
}
$query = "<b>DROP TABLE `purchases</b>`";
$result = $connection-&gt;query($query);
if (DB::isError($result)){
die("Could not query the database: &lt;br /&gt;". $query." ".DB::errorMessage($result));
}
echo "Table dropped successfully!";
$connection-&gt;disconnect();
?&gt;
</pre><br>

</td></tr></table></p>
<p class="docText"><a class="docLink" href="#learnphpmysql-CHP-12-EX-2">Example 12-2</a> returns:</p>
<pre>
Table dropped successfully!
</pre><br>

<p class="docText">That worked great, but you're going to need the <tt>purchases</tt> table, so let's recreate the table by calling the <span class="docEmphasis">create_table.php</span> code in <a class="docLink" href="#learnphpmysql-CHP-12-EX-1">Example 12-1</a>.</p>

<a name="learnphpmysql-CHP-12-SECT-1.3"></a>
<h4 id="title-IDADJE1L" class="docSection2Title">12.1.3. Errors Happen</h4>
<p class="docText">To make sure you handle an error properlysuch as a typo in the <tt>create</tt> statement or, in this case, trying to create a table that already existsexecute the <span class="docEmphasis">create_table.php</span> script again. This produces the error in <a class="docLink" href="#learnphpmysql-CHP-12-FIG-2">Figure 12-2</a>.</p>
<a name="learnphpmysql-CHP-12-FIG-2"></a><p><center>
<h5 class="docFigureTitle">Figure 12-2. Attempting to create an existing table generates this error</H5>
<img border="0" alt="" width="549" height="114" SRC="images/learnphpmysql_1202.jpg">
</center></p><br>
<p class="docText">Next, you'll add data to an existing table based on input from the user.</P>


</TD></TR></table>
<br>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr><td><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12.html><img src="images/prev.gif" width="60" height="17" border="0" align="absmiddle" alt="Previous Page"></a>
<td align="right"><div STYLE="MARGIN-LEFT: 0.15in;">
<a href=learnphpmysql-CHP-12-SECT-2.html><img src="images/next.gif" width="60" height="17" border="0" align="absmiddle" alt="Next Page"></a>
</div></td></tr></table>
<script type="text/javascript"><!--
google_ad_client = "pub-0203281046321155";
google_alternate_ad_url = "http://www.bookhtml.com/adbrite.htm";
google_ad_width = 728;
google_ad_height = 90;
google_ad_format = "728x90_as";
google_ad_type = "text_image";
google_ad_channel ="4867465545";
google_color_border = "FFFFFF";
google_color_link = "0000FF";
google_color_bg = "FFFFFF";
google_color_text = "000000";
google_color_url = "0000FF";
//--></script>
<script type="text/javascript"
  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</html>
